CREATE DATABASE Stores
GO
USE  Stores
GO

CREATE TABLE Units(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	del_flg int not null
)

go 

CREATE TABLE Groups(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	details nvarchar(300) not null,
	del_flg int not null
)

go

CREATE TABLE Drugs(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	price float not null,
	id_unit int not null FOREIGN KEY REFERENCES Units(Id),
	manual nvarchar(300) not null,
	number_of_day int not null,
	id_group int not null FOREIGN KEY REFERENCES Groups(Id),
	del_flg int not null
)

go 

CREATE TABLE Recommend(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	recommend nvarchar(300) not null,
	advice int not null,
	del_flg int not null
)

go

CREATE TABLE Prescription(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	id_drug_1 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_2 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_3 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_4 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_5 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_6 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_7 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_8 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_9 int null FOREIGN KEY REFERENCES Drugs(id),
	id_drug_10 int null FOREIGN KEY REFERENCES Drugs(id)
)

go

CREATE TABLE Patients(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	sex int not null,
	brithday datetime not null,
	phone nvarchar(20) not null,
	address nvarchar(100) not null,
	del_flg int not null
)

go

CREATE TABLE BILL(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	id_Prescription int not null FOREIGN KEY REFERENCES Prescription(id),
	id_Patients int not null FOREIGN KEY REFERENCES Patients(id),
	priceBill float not null
)

go

CREATE TABLE DrugImport(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	nameDrug nvarchar(100) not null,
	id_unit int not null FOREIGN KEY REFERENCES Units(id),
	price float not null,
	numbers int not null,
	import_price float not null,
	sum_price float not null
)

go

CREATE TABLE Warehouse(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	personImport nvarchar(100) not null,
	DateImport datetime not null,
	priceAll float not null
)